﻿using Dapper;
using Domain.Entitys;
using Domain.Repositorys;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace EFCore.Repositorys
{
    public class SSRepository : ISSRepository
    {
        private readonly MyDbContext myDbContext;
        string str = "Data Source=.;Initial Catalog=HR_DB;Integrated Security=True;Connect Timeout=30;Encrypt=False;";

        public SSRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        /// <summary>
        /// 添加薪酬标准信息表
        /// </summary>
        /// <param name="sS"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> SSAddAsync(SS sS)
        {
            //sS.ChangeStatus = "等待复核";
            sS.CheckStatus = "等待复核";
            myDbContext.SS.Add(sS);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 修改变更信息
        /// </summary>
        /// <param name="sS"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> SSChangeUpdateAsync(SS sS)
        {
            myDbContext.SS.Update(sS);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据编号查询薪酬标准信息表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<SS> SSSelectByIdAsync(string id)
        {
            return await myDbContext.SS.FirstOrDefaultAsync(s => s.StandardId == id);
        }

        /// <summary>
        /// 根据薪酬标准主键查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<SS> SSSelectBySsdId(int id)
        {
            return await myDbContext.SS.FirstOrDefaultAsync(s => s.SsdId == id);
        }

        /// <summary>
        /// 查询等待复核数据
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<SS> SSSelectFenYe(FenYe<SS> fenYe, string state)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize",fenYe.PageSize);
                parameters.Add("@keyName", "SsdId");
                parameters.Add("@tabelName", "SS");
                parameters.Add("@where", $"CheckStatus = '{state}'");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<SS> sS = sc.Query<SS>(sql,parameters).ToList();
                fenYe.list = sS;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }

        /// <summary>
        /// 多条件分页查询
        /// </summary>
        /// <param name="fenYe"></param>
        /// <param name="id">薪酬标准编号</param>
        /// <param name="name">关键人</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<SS> SSSelectWhere(FenYe<SS> fenYe, string id, string name, string startDate, string endDate, string state)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                string where = $@"[StandardId] like '%{id}%' and ([Designer] like '%{name}%' OR [Changer] like '%{name}%' OR [Checker] like '%{name}%') ";
                if (startDate != "" && endDate != "")
                {
                    where += $@" AND ( [RegistTime] > '{startDate}' and [RegistTime] < '{endDate}')";
                }
                if (state != "")
                {
                    where += $" and [CheckStatus] = '{state}'";
                }
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", fenYe.PageSize);
                parameters.Add("@keyName", "SsdId");
                parameters.Add("@tabelName", "SS");
                parameters.Add("@where", where);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<SS> sS = sc.Query<SS>(sql, parameters).ToList();
                fenYe.list = sS;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }

        /// <summary>
        /// 通过复核
        /// </summary>
        /// <param name="sS"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> SSThroughReviewAsync(SS sS)
        {
            myDbContext.SS.Update(sS);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 查询全部薪酬标准信息
        /// </summary>
        /// <returns></returns>
        public Task<List<SS>> SSFill()
        {
            return myDbContext.SS.Where(s=> s.CheckStatus == "通过复核").ToListAsync();
        }
        public async Task<List<SS>> GetSS()
        {
            return await myDbContext.SS.Where(e => e.CheckStatus == "通过复核").ToListAsync();
        }

        public FenYe<SS> SSSelectWhere2(FenYe<SS> fenYe, string id, string name, string startDate, string endDate, string state)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                string where = $@"[StandardId] like '%{id}%' and ([Designer] like '%{name}%' OR [Changer] like '%{name}%' OR [Checker] like '%{name}%') ";
                if (startDate != "" && endDate != "")
                {
                    where += $@" AND ( [RegistTime] > '{startDate}' and [RegistTime] < '{endDate}')";
                }
                if (state != "")
                {
                    where += $" and [CheckStatus] = '{state}'";
                }
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", fenYe.PageSize);
                parameters.Add("@keyName", "SsdId");
                parameters.Add("@tabelName", "SS");
                parameters.Add("@where", where);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<SS> sS = sc.Query<SS>(sql, parameters).ToList();
                fenYe.list = sS;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }
    }
}
